from collections import defaultdict
from datetime import datetime

from sqlalchemy import func, or_, and_, exc

from app.config.db import DBUtils
from app.model.BusinessInfoModel import BusinessInfo
from app.model.ChipbModel import ChipbModel
from app.model.ChupbModel import ChupbModel
from app.model.FKListModel import fklist
from app.model.SKListModel import sklist

db_utils = DBUtils()

class SettlementPreferenceService:

    def __init__(self):
        self.db = DBUtils()

    # 交易指标
    def select_trading_indicators_data(self, session, companyName, company_type, start_date, end_date):
        if (start_date, end_date) not in (None, ''):
            year = datetime.now().year
        else:
            year = start_date[:4]
        if companyName is not None:
            chip_je_data = None
            chup_je_data = None
            sy_data = None
            xy_data = None
            if company_type == 1: # 成员单位
                try:
                    # 票据结算
                    # 持票总额数据
                    chip_je_data = session.query(func.sum(ChipbModel.PMJE)).filter(ChipbModel.CHIPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).all()
                    # 出票总额数据
                    chup_je_data = session.query(func.sum(ChupbModel.PMJE)).filter(ChupbModel.CHUPRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).all()
                    # 现金结算
                    # 获取上游交易总额
                    sy_data = session.query(func.sum(sklist.FKJE)).select_from(sklist).outerjoin(
                        BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        sklist.SKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).all()
                    # 获取下游交易总额
                    xy_data = session.query(func.sum(fklist.FKJE)).select_from(fklist).outerjoin(
                        BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        fklist.FKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).all()
                except exc.SQLAlchemyError as e:
                    error_response = [{'error': str(e)}]
                    try:
                        session.rollback()
                    except Exception as rollback_error:
                        print(f"Failed to rollback transaction: {rollback_error}")
                    return error_response
                finally:
                    try:
                        db_utils.remove_session()
                    except Exception as remove_error:
                        print(f"Failed to rollback transaction: {remove_error}")
            else:
                try:
                    # 票据结算
                    # 持票总额数据
                    chip_je_data = session.query(func.sum(ChipbModel.PMJE)).filter(ChipbModel.CHUPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).all()
                    # 出票总额数据
                    chup_je_data = session.query(func.sum(ChupbModel.PMJE)).filter(ChupbModel.SKRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).all()
                    # 现金结算
                    # 获取上游交易总额和交易笔数
                    sy_data = session.query(func.sum(fklist.FKJE)).select_from(fklist).outerjoin(
                        BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        fklist.FKZHMC == companyName,
                        fklist.SCRQ >= start_date,  # 起始日期
                        fklist.SCRQ <= end_date  # 截止日期
                    ).all()
                    # 获取下游交易总额和交易笔数
                    xy_data = session.query(func.sum(sklist.FKJE)).select_from(sklist).outerjoin(
                        BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        sklist.SKZHMC == companyName,
                        sklist.SCRQ >= start_date,  # 起始日期
                        sklist.SCRQ <= end_date  # 截止日期
                    ).all()
                except exc.SQLAlchemyError as e:
                    error_response = [{'error': str(e)}]
                    try:
                        session.rollback()
                    except Exception as rollback_error:
                        print(f"Failed to rollback transaction: {rollback_error}")
                    return error_response
                finally:
                    try:
                        db_utils.remove_session()
                    except Exception as remove_error:
                        print(f"Failed to rollback transaction: {remove_error}")
            chip_je_filter_data = [item for item in chip_je_data if item != (None,)]
            chup_je_filter_data = [item for item in chup_je_data if item != (None,)]
            # 持票总额
            if len(chip_je_filter_data) > 0:
                chip_total_amount = chip_je_filter_data[0][0]
            else:
                chip_total_amount = 0
            # 出票总额
            if len(chup_je_filter_data) > 0:
                chup_total_amount = chup_je_filter_data[0][0]
            else:
                chup_total_amount = 0
            # 票据总额
            if chip_total_amount is not None and chup_total_amount is not None:
                pj_total_amount = chip_total_amount + chup_total_amount
            else:
                pj_total_amount = 0
            # 上游交易总额
            sy_total_amount = sy_data[0][0]
            # 下游交易总额
            xy_total_amount = xy_data[0][0]
            # 现金总额
            if sy_total_amount is not None and xy_total_amount is not None:
                xj_total_amount = sy_total_amount + xy_total_amount
            else:
                xj_total_amount = 0
            # 所有交易总额
            all_total_amount = pj_total_amount + xj_total_amount
            if pj_total_amount > xj_total_amount:
                # 结算方式
                js_method = "票据"
                # 交易总额占比
                jy_ratio = str(round((pj_total_amount / all_total_amount)*100,2)) + '%'
            elif xj_total_amount > pj_total_amount:
                # 结算方式
                js_method = "现金"
                # 交易总额占比
                jy_ratio = str(round((xj_total_amount / all_total_amount) * 100, 2)) + '%'
            else:
                js_method = "无偏好"
                jy_ratio = '50%'
            if js_method == "票据":
                chip_avg_month_data = None
                chup_avg_month_data = None
                chip_js_like_data = None
                chup_js_like_data = None
                if company_type == 1:  # 成员单位
                    try:
                        # 持票平均周转
                        chip_avg_month_data = session.query(func.round(func.avg(ChipbModel.DQR - ChipbModel.CPR)/30,2)).filter(ChipbModel.CHIPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).group_by(ChipbModel.DQR,ChipbModel.CPR).all()
                        # 出票平均周转
                        chup_avg_month_data = session.query(func.round(func.avg(ChupbModel.DQR - ChupbModel.CPR)/30,2)).filter(ChupbModel.CHUPRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).group_by(ChupbModel.DQR,ChupbModel.CPR).all()
                        # 持票结算偏好
                        chip_js_like_data = session.query(ChipbModel.PJLB,func.count(ChipbModel.PJLB)).filter(ChipbModel.CHIPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).group_by(ChipbModel.PJLB).all()
                        # 出票结算偏好
                        chup_js_like_data = session.query(ChupbModel.PJLB,func.count(ChupbModel.PJLB)).filter(ChupbModel.CHUPRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).group_by(ChupbModel.PJLB).all()
                    except exc.SQLAlchemyError as e:
                        error_response = [{'error': str(e)}]
                        try:
                            session.rollback()
                        except Exception as rollback_error:
                            print(f"Failed to rollback transaction: {rollback_error}")
                        return error_response
                    finally:
                        try:
                            db_utils.remove_session()
                        except Exception as remove_error:
                            print(f"Failed to rollback transaction: {remove_error}")
                else:
                    try:
                        # 持票平均周转
                        chip_avg_month_data = session.query(func.round(func.avg(ChipbModel.DQR - ChipbModel.CPR) / 30, 2)).filter(ChipbModel.CHUPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).group_by(ChipbModel.DQR, ChipbModel.CPR).all()
                        # 出票平均周转
                        chup_avg_month_data = session.query(func.round(func.avg(ChupbModel.DQR - ChupbModel.CPR) / 30, 2)).filter(ChupbModel.SKRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).group_by(ChupbModel.DQR, ChupbModel.CPR).all()
                        # 持票结算偏好
                        chip_js_like_data = session.query(ChipbModel.PJLB, func.count(ChipbModel.PJLB)).filter(ChipbModel.CHUPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).group_by(ChipbModel.PJLB).all()
                        # 出票结算偏好
                        chup_js_like_data = session.query(ChupbModel.PJLB, func.count(ChupbModel.PJLB)).filter(ChupbModel.SKRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).group_by(ChupbModel.PJLB).all()
                    except exc.SQLAlchemyError as e:
                        error_response = [{'error': str(e)}]
                        try:
                            session.rollback()
                        except Exception as rollback_error:
                            print(f"Failed to rollback transaction: {rollback_error}")
                        return error_response
                    finally:
                        try:
                            db_utils.remove_session()
                        except Exception as remove_error:
                            print(f"Failed to rollback transaction: {remove_error}")
                avg_month_list = []
                for item in chip_avg_month_data:
                    avg_month_list.append(item[0])
                for item in chup_avg_month_data:
                    avg_month_list.append(item[0])
                # 平均票据周转时间
                avg_month = str(round(sum(avg_month_list)/len(avg_month_list)))
                total = defaultdict(float)
                for item in chip_js_like_data + chup_js_like_data:
                    if item[0] == 'AC01':
                        total['银行承兑汇票'] += item[1]
                    elif item[0] == 'AC02':
                        total['商业承兑汇票'] += item[1]

                # 决定哪个值更大
                if not total:
                    js_like = None
                elif total['银行承兑汇票'] > total['商业承兑汇票']:
                    js_like = '银行承兑汇票'
                else:
                    js_like = '商业承兑汇票'
                result = {
                    'js_method': js_method, # 结算方式
                    'jy_ratio': jy_ratio, # 交易总额占比
                    'avg_month': avg_month, # 平均票据周转时间
                    'js_like': js_like, # 结算偏好
                }
            elif js_method == "现金":
                result = {
                    'js_method': js_method,  # 结算方式
                    'jy_ratio': jy_ratio,  # 交易总额占比
                }
            else:
                result = {
                    'js_method': js_method,  # 结算方式
                    'jy_ratio': jy_ratio,  # 交易总额占比
                }
            return result

    # 结算统计
    def select_settlement_statistics_data(self, session, companyName, company_type, start_date, end_date):
        if (start_date, end_date) not in (None, ''):
            year = datetime.now().year
        else:
            year = start_date[:4]
        months = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
        if companyName is not None:
            chip_y_je_data = None
            chup_y_je_data = None
            sy_jy_data = None
            xy_jy_data = None
            if company_type == 1: # 成员单位
                try:
                    # 票据结算
                    # 持票月总额数据
                    chip_y_je_data = session.query(func.month(ChipbModel.CPR),func.sum(ChipbModel.PMJE)).filter(ChipbModel.CHIPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).group_by(func.month(ChipbModel.CPR)).all()
                    # 出票月总额数据
                    chup_y_je_data = session.query(func.month(ChupbModel.CPR),func.sum(ChupbModel.PMJE)).filter(ChupbModel.CHUPRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).group_by(func.month(ChupbModel.CPR)).all()
                    # 现金结算
                    # 每月上游交易金额
                    sy_jy_data = session.query(func.month(sklist.SCRQ), func.sum(sklist.FKJE)).select_from(
                        sklist).outerjoin(BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            sklist.SKZHMC == companyName,
                            sklist.SCRQ >= start_date,  # 起始日期
                            sklist.SCRQ <= end_date  # 截止日期
                        )).group_by(func.month(sklist.SCRQ)).all()
                    # 每月下游交易金额
                    xy_jy_data = session.query(func.month(fklist.SCRQ), func.sum(fklist.FKJE)).select_from(
                        fklist).outerjoin(BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            fklist.FKZHMC == companyName,
                            fklist.SCRQ >= start_date,  # 起始日期
                            fklist.SCRQ <= end_date  # 截止日期
                        )).group_by(func.month(fklist.SCRQ)).all()
                except exc.SQLAlchemyError as e:
                    error_response = [{'error': str(e)}]
                    try:
                        session.rollback()
                    except Exception as rollback_error:
                        print(f"Failed to rollback transaction: {rollback_error}")
                    return error_response
                finally:
                    try:
                        db_utils.remove_session()
                    except Exception as remove_error:
                        print(f"Failed to rollback transaction: {remove_error}")
            else:
                try:
                    # 票据结算
                    # 持票月总额数据
                    chip_y_je_data = session.query(func.month(ChipbModel.CPR),func.sum(ChipbModel.PMJE)).filter(ChipbModel.CHUPRQC == companyName,ChipbModel.CPR >= start_date,ChipbModel.CPR <= end_date).group_by(func.month(ChipbModel.CPR)).all()
                    # 出票月总额数据
                    chup_y_je_data = session.query(func.month(ChupbModel.CPR),func.sum(ChupbModel.PMJE)).filter(ChupbModel.SKRQC == companyName,ChupbModel.CPR >= start_date,ChupbModel.CPR <= end_date).group_by(func.month(ChupbModel.CPR)).all()
                    # 现金结算
                    # 每月上游交易金额
                    sy_jy_data = session.query(func.month(fklist.SCRQ), func.sum(fklist.FKJE)).select_from(
                        fklist).outerjoin(BusinessInfo, fklist.SKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            fklist.FKZHMC == companyName,
                            fklist.SCRQ >= start_date,  # 起始日期
                            fklist.SCRQ <= end_date  # 截止日期
                        )).group_by(func.month(fklist.SCRQ)).all()
                    # 每月下游交易金额
                    xy_jy_data = session.query(func.month(sklist.SCRQ), func.sum(sklist.FKJE)).select_from(
                        sklist).outerjoin(BusinessInfo, sklist.FKZHMC == BusinessInfo.GSMC).filter(
                        and_(
                            sklist.SKZHMC == companyName,
                            sklist.SCRQ >= start_date,  # 起始日期
                            sklist.SCRQ <= end_date  # 截止日期
                        )).group_by(func.month(sklist.SCRQ)).all()
                except exc.SQLAlchemyError as e:
                    error_response = [{'error': str(e)}]
                    try:
                        session.rollback()
                    except Exception as rollback_error:
                        print(f"Failed to rollback transaction: {rollback_error}")
                    return error_response
                finally:
                    try:
                        db_utils.remove_session()
                    except Exception as remove_error:
                        print(f"Failed to rollback transaction: {remove_error}")
            # 票据每月交易金额
            pj_y_je_list = []
            for month in months:
                md = {}
                if len(chip_y_je_data) > 0:
                    for mon, value in chip_y_je_data:
                        if mon == month:
                            md['name'] = mon
                            md['value'] = round(value / 10000, 2)
                            break
                        else:
                            md['name'] = month
                            md['value'] = 0
                else:
                    md['name'] = month
                    md['value'] = 0
                pj_y_je_list.append(md)
            for item in pj_y_je_list:
                if len(chup_y_je_data) > 0:
                    for mon, value in chup_y_je_data:
                        if mon == item['name']:
                            item['value'] += round(value / 10000, 2)
            # 每月上下游交易金额
            xj_y_je_list = []
            for month in months:
                md = {}
                if len(sy_jy_data) > 0:
                    for mon, value in sy_jy_data:
                        if mon == month:
                            md['name'] = mon
                            md['value'] = round(value / 10000, 2)
                            break
                        else:
                            md['name'] = month
                            md['value'] = 0
                else:
                    md['name'] = month
                    md['value'] = 0
                xj_y_je_list.append(md)
            for item in xj_y_je_list:
                if len(xy_jy_data) > 0:
                    for mon, value in xy_jy_data:
                        if mon == item['name']:
                            item['value'] += round(value / 10000, 2)
            result = {
                'months': months, # 总月份
                'pj_y_je_list': pj_y_je_list, # 票据每月金额
                'xj_y_je_list': xj_y_je_list, # 现金每月金额
            }
        else:
            result = None
        return result





